﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DAL
{
    public class KhachHangDAO
    {
        public int CountMatchFilter(String[] filter)
        {
            String[] fl = new String[] {
                DAL.sqlHelper.FormatStringToSearch(filter[0]), //hoten
                DAL.sqlHelper.FormatStringToSearch(filter[1]), //diachi
                DAL.sqlHelper.FormatStringToSearch(filter[2]), //dienthoai
                DAL.sqlHelper.FormatStringToSearch(filter[3])  //email 
            };

            try
            {
                List<System.Data.SqlClient.SqlParameter> prs =
                    new List<System.Data.SqlClient.SqlParameter>();
                prs.Add(new System.Data.SqlClient.SqlParameter("@HoTen", fl[0]));
                prs.Add(new System.Data.SqlClient.SqlParameter("@DiaChi", fl[1]));
                prs.Add(new System.Data.SqlClient.SqlParameter("@DienThoai", fl[2]));
                prs.Add(new System.Data.SqlClient.SqlParameter("@Email", fl[3]));
                System.Data.DataTable dt =
                    DAL.sqlHelper.ExecuteQuery("KhachHang_Cnt_Filter", prs);
                if (dt.Rows.Count > 0)
                    return int.Parse(dt.Rows[0]["SL"].ToString());
                else
                    return 0;

            }
            catch (System.Exception ex)
            {
                throw new System.Exception("CountMatchFilter failed " +
                ex.Message);
            }
            finally
            {

            }
        }

        public List<DTO.KhachHangDTO> MatchFilterPerPage(int page, String[] filter)
        {
            String[] fl = new String[] {
                DAL.sqlHelper.FormatStringToSearch(filter[0]), //hoten
                DAL.sqlHelper.FormatStringToSearch(filter[1]), //diachi
                DAL.sqlHelper.FormatStringToSearch(filter[2]), //dienthoai
                DAL.sqlHelper.FormatStringToSearch(filter[3])  //email 
            };
            List<DTO.KhachHangDTO> r = new List<DTO.KhachHangDTO>();
            try
            {
                List<System.Data.SqlClient.SqlParameter> prs =
                    new List<System.Data.SqlClient.SqlParameter>();
                prs.Add(new System.Data.SqlClient.SqlParameter("@Page", page));
                prs.Add(new System.Data.SqlClient.SqlParameter("@HoTen", fl[0]));
                prs.Add(new System.Data.SqlClient.SqlParameter("@DiaChi", fl[1]));
                prs.Add(new System.Data.SqlClient.SqlParameter("@DienThoai", fl[2]));
                prs.Add(new System.Data.SqlClient.SqlParameter("@Email", fl[3]));
                System.Data.DataTable dt =
                    DAL.sqlHelper.ExecuteQuery("KhachHang_Filter", prs);

                foreach (System.Data.DataRow dr in dt.Rows)
                {
                    DTO.KhachHangDTO dto = new DTO.KhachHangDTO();
                    dto.ID = int.Parse(dr["ID"].ToString());
                    dto.HoTen = dr["HoTen"].ToString();
                    dto.DiaChi = dr["DiaChi"].ToString();
                    dto.DienThoai = dr["DienThoai"].ToString();
                    dto.Email = dr["Email"].ToString();
                    dto.DuNo = float.Parse(dr["DuNo"].ToString());
                    r.Add(dto);
                }
                return r;
            }
            catch (System.Exception ex)
            {
                throw new System.Exception("MatchFilterPerPage failed " + "page = " +
                page.ToString() + " " + ex.Message);
            }
            finally
            {

            }
        }

        public int Update(
            System.Data.SqlClient.SqlConnection connection,
            System.Data.SqlClient.SqlTransaction transaction,
            DTO.KhachHangDTO dto)
        {
            int r = -1;
            try
            {
                List<System.Data.SqlClient.SqlParameter> prs =
                   new List<System.Data.SqlClient.SqlParameter>();
                prs.Add(new System.Data.SqlClient.SqlParameter("@ID", dto.ID));
                prs.Add(new System.Data.SqlClient.SqlParameter("@HoTen", dto.HoTen));
                prs.Add(new System.Data.SqlClient.SqlParameter("@DiaChi", dto.DiaChi));
                prs.Add(new System.Data.SqlClient.SqlParameter("@DienThoai", dto.DienThoai));
                prs.Add(new System.Data.SqlClient.SqlParameter("@Email", dto.Email));
                prs.Add(new System.Data.SqlClient.SqlParameter("@DuNo", dto.DuNo));
                if (connection == null || transaction == null)
                    r = DAL.sqlHelper.ExecuteNoneQuery("KhachHang_UpdateByID", prs);
                else
                    r = DAL.sqlHelper.ExecuteNoneQuery(connection, transaction, "KhachHang_UpdateByID", prs);
                return r;
            }
            catch (System.Exception ex)
            {
                throw new System.Exception("Update failed " + ex.Message);
            }
            finally
            {
            }
        }

        public int Exists(
            System.Data.SqlClient.SqlConnection connection,
            System.Data.SqlClient.SqlTransaction transaction,
            ref DTO.KhachHangDTO dto)
        {
            int r = -1;
            try
            {
                List<System.Data.SqlClient.SqlParameter> prs =
                    new List<System.Data.SqlClient.SqlParameter>();
                prs.Add(new System.Data.SqlClient.SqlParameter("@HoTen", dto.HoTen));
                prs.Add(new System.Data.SqlClient.SqlParameter("@DiaChi", dto.DiaChi));
                prs.Add(new System.Data.SqlClient.SqlParameter("@DienThoai", dto.DienThoai));
                prs.Add(new System.Data.SqlClient.SqlParameter("@Email", dto.Email));
                System.Data.DataTable dt = null;
                if (connection == null || transaction == null)
                    dt = DAL.sqlHelper.ExecuteQuery("KhachHang_SelectByTenDiaChiEmailDienThoai", prs);
                else
                    dt = DAL.sqlHelper.ExecuteQuery(connection, transaction, "KhachHang_SelectByTenDiaChiEmailDienThoai", prs);
                if (dt.Rows.Count < 1)
                    r = -1;
                else
                {
                    System.Data.DataRow dr = dt.Rows[0];
                    dto.ID = int.Parse(dr["ID"].ToString());
                    dto.HoTen = dr["HoTen"].ToString();
                    dto.DiaChi = dr["DiaChi"].ToString();
                    dto.DienThoai = dr["DienThoai"].ToString();
                    dto.Email = dr["Email"].ToString();
                    dto.DuNo = float.Parse(dr["DuNo"].ToString());
                    r = dto.ID;
                }
                return r;
            }
            catch (System.Exception ex)
            {
                throw new System.Exception("Exists failed " + ex.Message);
            }
            finally
            {
            }
        }

        public int Insert(
            System.Data.SqlClient.SqlConnection connection,
            System.Data.SqlClient.SqlTransaction transaction,
            DTO.KhachHangDTO dto)
        {
            int r = -1;
            try
            {
                List<System.Data.SqlClient.SqlParameter> prs =
                   new List<System.Data.SqlClient.SqlParameter>();
                prs.Add(new System.Data.SqlClient.SqlParameter("@HoTen", dto.HoTen));
                prs.Add(new System.Data.SqlClient.SqlParameter("@DiaChi", dto.DiaChi));
                prs.Add(new System.Data.SqlClient.SqlParameter("@DienThoai", dto.DienThoai));
                prs.Add(new System.Data.SqlClient.SqlParameter("@Email", dto.Email));
                prs.Add(new System.Data.SqlClient.SqlParameter("@DuNo", dto.DuNo));
                if (connection == null || transaction == null)
                    r = DAL.sqlHelper.ExecuteNoneQuery("KhachHang_Insert", prs);
                else
                    r = DAL.sqlHelper.ExecuteNoneQuery(connection, transaction, "KhachHang_Insert", prs);
                return r;
            }
            catch (System.Exception ex)
            {
                throw new System.Exception("Insert failed " + ex.Message);
            }
            finally
            {
            }
        }

        public int Select(
            System.Data.SqlClient.SqlConnection connection,
            System.Data.SqlClient.SqlTransaction transaction,
            ref DTO.KhachHangDTO dto)
        {
            int r = -1;
            try
            {
                List<System.Data.SqlClient.SqlParameter> prs =
                    new List<System.Data.SqlClient.SqlParameter>();
                prs.Add(new System.Data.SqlClient.SqlParameter("@ID", dto.ID));
                System.Data.DataTable dt = null;
                if (connection == null || transaction == null)
                    dt = DAL.sqlHelper.ExecuteQuery("KhachHang_SelectByID", prs);
                else
                    dt = DAL.sqlHelper.ExecuteQuery(connection, transaction, "KhachHang_SelectByID", prs);
                if (dt.Rows.Count < 1)
                    r = -1;
                else
                {
                    System.Data.DataRow dr = dt.Rows[0];
                    dto.HoTen = dr["HoTen"].ToString();
                    dto.DiaChi = dr["DiaChi"].ToString();
                    dto.DienThoai = dr["DienThoai"].ToString();
                    dto.Email = dr["Email"].ToString();
                    dto.DuNo = float.Parse(dr["DuNo"].ToString());
                    r = dto.ID;
                }
                return r;
            }
            catch (System.Exception ex)
            {
                throw new System.Exception("Exists failed " + ex.Message);
            }
            finally
            {
            }
        }
    }
}
